package com.zzxz.practicaltrainingsys.mapper;

import com.zzxz.practicaltrainingsys.entity.Team;
import com.zzxz.practicaltrainingsys.vo.ViewTeam;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.web.bind.annotation.RequestBody;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface TeamMapper {

    //判断角色
    @Select("SELECT COUNT(*) FROM team WHERE leader_id = #{studentId}")
    int countAsLeader(@Param("studentId") int studentId);

    @Select("SELECT COUNT(*) FROM team WHERE viceleader_id = #{studentId}")
    int countAsViceLeader(@Param("studentId") int studentId);

    @Select("SELECT COUNT(*) FROM member WHERE student_id = #{studentId}")
    int countAsMember(@Param("studentId") int studentId);


    //在组队信息表（team）里插入组长学号、组名、选题编号、邀请码、项目编号、小组组号。
    @Insert("insert into team(leader_id,team_name,topic_id,invite_code,project_id,group_id) values(#{leader_id},#{team_name},#{topic_id},#{invite_code},#{project_id},#{group_id})")
    int insertTeam(@RequestBody Team team);

    //插入副组长，根据组号更新team表中的viceleader_id（副组长）字段
    @Update("update team set viceleader_id=#{viceleader_id} where team_id=#{team_id}")
    int updateViceleaderId(@RequestBody Team team);

    //在组队信息表（team）根据组长学号查取邀请码。
    @Select("select invite_code from team where leader_id=#{leader_id}")
    String getInviteCodeByLeaderId(Integer leader_id);

    //根据组号在组队信息表（team）插入小组分数
    @Update("update team set group_marks=#{group_marks} where group_id=#{group_id}")
    int updateGroupMarks(@RequestBody Team team);

    //根据组号team_id在组队信息表（team）查询小组成绩
    @Select("select group_marks from team where team_id=#{team_id}")
    String getGroupMarksByTeamId(Integer group_id);

    //查找所有的组队信息
    @Select("SELECT \n" +
            "  p.project_name,\n" +
            "  t.team_id,\n" +
            "  t.team_name,\n" +
            "  t.leader_id,\n" +
            "  s_leader.student_name AS leader_name,\n" +
            "  t.viceleader_id,\n" +
            "  s_vice.student_name AS viceleader_name,\n" +
            "  t.invite_code,\n" +
            "  tp.topic_name,\n" +
            "  GROUP_CONCAT(DISTINCT s_member.student_name SEPARATOR ', ') AS member_names\n" +
            "FROM team t\n" +
            "LEFT JOIN project p ON t.project_id = p.project_id\n" +
            "LEFT JOIN topic tp ON t.topic_id = tp.topic_id\n" +
            "LEFT JOIN student s_leader ON t.leader_id = s_leader.student_id\n" +
            "LEFT JOIN student s_vice ON t.viceleader_id = s_vice.student_id\n" +
            "LEFT JOIN member m ON t.team_id = m.team_id\n" +  // 这里改为 team_id
            "LEFT JOIN student s_member ON m.student_id = s_member.student_id\n" +
            "  AND m.student_id NOT IN (t.leader_id, t.viceleader_id)\n" +
            "GROUP BY t.team_id\n" +
            "LIMIT #{offset}, #{pageSize}")
    List<ViewTeam> getViewTeamsPage(@Param("offset") int offset, @Param("pageSize") int pageSize);

    //查询所有小队数量
    @Select("SELECT COUNT(DISTINCT t.team_id) FROM team t")
    int getTotalCount();


    //实时聊天实现按邀请码进行分组聊天
    @Select("SELECT COUNT(*) FROM team WHERE invite_code = #{inviteCode}")
    int countByInviteCode(String inviteCode);

    //根据选题ID统计该选题下的小队数量
    @Select("SELECT COUNT(*) FROM team WHERE topic_id = #{topic_id}")
    int countByTopicId(Integer topic_id);

    //获取某项目下最大的group_id
    @Select("SELECT IFNULL(MAX(group_id), 0) FROM team WHERE project_id = #{project_id}")
    int getMaxGroupIdByProjectId(@Param("project_id") Integer project_id);

    //获取最近插入的team_id
    @Select("SELECT LAST_INSERT_ID()")
    Integer getLastInsertTeamId();

    //通过邀请码查找队伍
    @Select("SELECT * FROM team WHERE invite_code = #{invite_code} LIMIT 1")
    Team findByInviteCode(@Param("invite_code") String invite_code);


    @Select("SELECT project_id FROM team WHERE team_id = #{team_id}")
    Integer getProjectIdByTeamId(@Param("team_id") Integer team_id);

    @Select("SELECT team_id FROM team WHERE project_id = #{project_id}")
    List<Integer> getTeamIdsByProjectId(@Param("project_id") Integer project_id);

    @Select("SELECT COUNT(*) FROM team WHERE project_id = #{project_id} AND topic_id = #{topic_id}")
    int countByProjectAndTopic(@Param("project_id") Integer project_id, @Param("topic_id") Integer topic_id);


    @Select("SELECT * FROM team WHERE team_id = #{team_id} LIMIT 1")
    Team getTeamInfo(@Param("team_id") Integer team_id);


    //根据学号和项目id查找邀请码
    @Select("SELECT t.invite_code, t.team_name, s.student_name, p.project_name " +
            "FROM team t " +
            "INNER JOIN member m ON t.team_id = m.team_id " +
            "INNER JOIN student s ON m.student_id = s.student_id " +
            "INNER JOIN project p ON t.project_id = p.project_id " +
            "WHERE m.student_id = #{studentId} AND t.project_id = #{projectId}")
    ViewTeam getTeamByStudentAndProject(@Param("studentId") Integer studentId,
                                        @Param("projectId") Integer projectId);

    @Select("SELECT * FROM team WHERE topic_id = #{topic_id}")
    List<Team> getTeamsByTopicId(@Param("topic_id") Integer topic_id);

    @Update("UPDATE team SET group_marks = #{group_marks} WHERE team_id = #{team_id}")
    int updateTeamScore(@Param("team_id") Integer team_id, @Param("group_marks") Double group_marks);

}
